SELECT *
FROM ApInvoicesInterface
WHERE DATEDIFF(DAY,INVOICE_DATE,GETDATE())=0
ORDER BY INVOICE_ID DESC
 

DECLARE @now DATE
SET @now = GETDATE()

SELECT 'day',@now,DATEDIFF(day,'20170501',@now),DATEDIFF(day,'20170701',@now)
SELECT 'month',@now,DATEDIFF(month,'20170501',@now),DATEDIFF(month,'20170701',@now)
SELECT 'year',@now,DATEDIFF(year,'20170501',@now),DATEDIFF(year,'20170701',@now)





DECLARE @now DATETIME 
SET @now = GETDATE()
 

--当年数据
SELECT *
FROM ApInvoicesInterface
WHERE DATEDIFF(YEAR,INVOICE_DATE,GETDATE()) = 0
ORDER BY INVOICE_ID DESC 

 

 

--当月数据
SELECT *
FROM ApInvoicesInterface
WHERE DATEDIFF(MONTH,INVOICE_DATE,GETDATE()) = 0
ORDER BY INVOICE_ID DESC 

 

 

--当日数据
SELECT *
FROM ApInvoicesInterface
WHERE DATEDIFF(DAY,INVOICE_DATE,GETDATE()) = 0
ORDER BY INVOICE_ID DESC

 

 

--昨天的数据
SELECT *
FROM ApInvoicesInterface
WHERE DATEDIFF(DAY,INVOICE_DATE,GETDATE()) = 1

 

 

--上个月数据

SELECT *
FROM ApInvoicesInterface
WHERE DATEDIFF(MONTH,INVOICE_DATE,GETDATE()) = 1
ORDER BY INVOICE_ID DESC 

 
 

DATEDIFF(datepart,startdate,enddate)

 

DATEDIFF() 函数返回两个日期之间的时间。

Startdate 起始日期
Enddate  结束日期

 
datepart 参数可以是下列的值:
year 
month 
week 
day 
hour 
minute 
second 
millisecond

 


SELECT 
   DATEDIFF(DAY,'20170520','20170627'), --相隔多少天
   DATEDIFF(MONTH,'20170520','20170627'),  --相隔多少月
   DATEDIFF(YEAR,'20170520','20170627') --相隔多少年


SELECT 
   DATEDIFF(DAY,'20170620','20170627'), --相隔多少天
   DATEDIFF(MONTH,'20170620','20170627'),  --相隔多少月
   DATEDIFF(YEAR,'20170620','20170627') --相隔多少年


SELECT 
   DATEDIFF(DAY,'20170620','20170527'), --相隔多少天
   DATEDIFF(MONTH,'20170620','20170527'),  --相隔多少月
   DATEDIFF(YEAR,'20170620','20170527') --相隔多少年




DATEADD ( datepart , number, date ) 
 

DECLARE @now DATE
SET @now = GETDATE()

 
SELECT 
   @now AS '当前时间',
   DATEADD(DAY,2,@now)  AS '+2DAY', 
   DATEADD(DAY,-2,@now) AS '-2DAY'

 

SELECT 
   @now AS '当前时间',
   DATEADD(MONTH,2,@now)  AS '+2MONTH',
   DATEADD(MONTH,-2,@now) AS '-2MONTH'


SELECT 
   @now AS '当前时间',
   DATEADD(YEAR,2,@now)  AS '+2YEAR',
   DATEADD(YEAR,-2,@now) AS '-2YEAR'

 

DECLARE @now DATE
SET @now = GETDATE()

 

SELECT 'day',@now,DATEDIFF(day,'20170501',@now),DATEDIFF(day,'20170701',@now)

SELECT 'month',@now,DATEDIFF(month,'20170501',@now),DATEDIFF(month,'20170701',@now)

SELECT 'year',@now,DATEDIFF(year,'20170501',@now),DATEDIFF(year,'20170701',@now)


DATEADD(datepart, number, date)

 

datepart 参数可以是下列的值:
year 
month 
week 
day 
hour 
minute 
second 
millisecond

